﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class SysReportDataTableDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public SysReportDataTableDAL()
        {

        }

        public string GetPurchaseRequestApproveItemWinner(int seq)
        {
            string strResult = "";
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                        + ", FORMAT(q.ITEM_QTY, 0) AS QTY"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", c.ICM_CON_NAME_TH"
                        + ", 'ราคาเหมาะสม' AS REMARKS"
                    + " FROM ICM_BIDDER_QUOTATION q"
                        + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                        + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                        + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM p ON q.ICM_PR_ITEM_SEQ = p.ICM_PR_ITEM_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE x ON p.BGM_EXP_SEQ  = x.BGM_EXP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON p.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                        + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                    + " WHERE p.ICM_PR_SEQ = @seq"
                        + " AND q.SELECTED = @yes_status";

			    ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
			    dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
				    {
					    if (i == 0)
                        {
                            strResult += dr[i]["ITEM_NAME"].ToString() + " จำนวน " + dr[i]["QTY"].ToString() + " " + dr[i]["ICM_UNIT_NAME"].ToString() + " "
                                + " จาก " + dr[i]["ICM_CON_NAME_TH"].ToString() + " เนื่องจาก " + dr[i]["REMARKS"].ToString() + " ";
					    
                        }
                        else
                        {
                            strResult += "และ " + dr[i]["ITEM_NAME"].ToString() + " จำนวน " + dr[i]["QTY"].ToString() + " " + dr[i]["ICM_UNIT_NAME"].ToString() + " "
                                + " จาก " + dr[i]["ICM_CON_NAME_TH"].ToString() + " เนื่องจาก " + dr[i]["REMARKS"].ToString() + " ";
                        }
				    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string GetPurchaseRequestApproveItemList(int seq)
        {
            string strResult = "";
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", FORMAT(p.ICM_PR_ITEM_QTY, 0) AS QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + " FROM ICM_PURCHASE_REQUEST_ITEM p"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON p.BGM_EXP_SEQ  = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON p.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE p.ICM_PR_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        if (i == 0)
                        {
                            strResult += dr[i]["ITEM_NAME"].ToString() + " จำนวน " + dr[i]["QTY"].ToString() + " " + dr[i]["ICM_UNIT_NAME"].ToString() + " ";
                        }
                        else
                        {
                            strResult += "และ " + dr[i]["ITEM_NAME"].ToString() + " จำนวน " + dr[i]["QTY"].ToString() + " " + dr[i]["ICM_UNIT_NAME"].ToString() + " ";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string GetPurchaseOrderNetAmount(int seq)
        {
            string strResult = "";
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT FORMAT(SUM(COALESCE(it.NET_AMOUNT,0)),2) AS SUM_AMOUNT"
                    + " FROM ICM_PURCHASE_ORDER_INSTALLMENT i"
                        + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT_ITEM it ON i.ICM_PO_INSTALL_SEQ = it.ICM_PO_INSTALL_SEQ"
                    + " WHERE i.ICM_PO_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    strResult = dr[0]["SUM_AMOUNT"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string GetPurchaseOrderPenalty(int seq)
        {
            string strDescr = "";
            string strResult = "";
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "select p.ICM_PO_PEN_NAME"
                        + ", pt.ICM_PEN_RATE_TYPE_NAME"
                        + ", pc.ICM_PEN_COUNT_NAME"
                        + ", FORMAT(p.ICM_PO_PEN_RATE,2) AS ICM_PO_PEN_RATE"
                        + ", FORMAT(COALESCE(p.ICM_PO_PEN_LEAST,0), 2) AS ICM_PO_PEN_LEAST"
                        + ", COALESCE(p.ICM_PO_PEN_LEAST,0) AS CHECK_LEAST"
                    + " from ICM_PURCHASE_ORDER_PENALTY p"
                        + " LEFT JOIN ICM_SYS_PENALTY_COUNT pc ON p.ICM_PEN_COUNT_SEQ = pc.ICM_PEN_COUNT_SEQ"
                        + " LEFT JOIN ICM_SYS_PENALTY_RATE_TYPE pt ON p.ICM_PEN_RATE_TYPE_SEQ = pt.ICM_PEN_RATE_TYPE_SEQ" 
                    + " WHERE p.ICM_PO_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        strDescr = dr[i]["ICM_PO_PEN_NAME"].ToString() + "จะต้องถูกปรับ" + dr[i]["ICM_PEN_COUNT_NAME"].ToString() + "ละ "
                            + dr[i]["ICM_PO_PEN_RATE"].ToString() + " " + dr[i]["ICM_PEN_RATE_TYPE_NAME"].ToString()
                            + (Convert.ToDouble(dr[i]["CHECK_LEAST"]) > 0 ? " และไม่ต่ำกว่าวันละ " + dr[i]["ICM_PO_PEN_LEAST"] + " บาท" : "");

                        if (i == 0)
                            strResult += strDescr;
                        else
                        {
                            if (i == dr.Count - 1)
                                strResult += " และ " + strDescr;
                            else
                                strResult += ", " + strDescr;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string GetPurchaseOrderDate(int seq)
        {
            string strDescr = "";
            string strResult = "";
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT ORDER_DATE"
                    + " FROM ICM_PURCHASE_ORDER"
                    + " WHERE ICM_PO_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        strResult = dr[i]["ORDER_DATE"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string[] GetPurchaseRequestDate(int seq)
        {
            string[] strResult = new string[4];
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pr.REQUIRE_DATE"
                        + ", pr.REQUIRE_DURATION"
                        + ", dt.ICM_DUR_TYPE_NAME"
                        + ", dc.ICM_DUR_COUNT_NAME"
                    + " FROM ICM_PURCHASE_REQUEST pr"
                        + " LEFT JOIN ICM_SYS_DURATION_COUNT dc ON pr.ICM_DUR_COUNT_SEQ = dc.ICM_DUR_COUNT_SEQ"
                        + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON pr.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                    + " WHERE pr.ICM_PR_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        strResult[0] = dr[i]["REQUIRE_DATE"].ToString();
                        strResult[1] = dr[i]["REQUIRE_DURATION"].ToString();
                        strResult[2] = dr[i]["ICM_DUR_TYPE_NAME"].ToString();
                        strResult[3] = dr[i]["ICM_DUR_COUNT_NAME"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string[] GetDeliveryNoteDate(int seq)
        {
            string[] strResult = new string[4];
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT po.APPROVED_WHEN"
                        + ", dn.RECEIVE_DATE"
                        + ", dn.DELIVERY_DATE"
                        + ", dn.INVOICE_DATE"
                    + " FROM ICM_DELIVERY_NOTE"
                    + " WHERE ICM_DN_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        strResult[0] = dr[i]["APPROVED_WHEN"].ToString();
                        strResult[1] = dr[i]["RECEIVE_DATE"].ToString();
                        strResult[2] = dr[i]["DELIVERY_DATE"].ToString();
                        strResult[3] = dr[i]["INVOICE_DATE"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string[] GetDeliveryNoteReceiveItem(int seq)
        {
            string strPoDescr = "";
            string strDnDescr = "";
            string[] strResult = new string[2];
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", FORMAT(SUM(q.ITEM_QTY),0) AS PO_QTY"
                        + ", FORMAT(SUM(q.NET_AMOUNT),2) AS PO_AMOUNT"
                        + ", FORMAT(SUM(ni.ITEM_QTY),0) AS DN_QTY"
                        + ", FORMAT(SUM(ni.NET_AMOUNT),2) AS DN_AMOUNT"
                    + " FROM ICM_DELIVERY_NOTE_ITEM ni"
                        + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT_ITEM ii ON ni.ICM_PO_INSTALL_ITEM_SEQ = ii.ICM_PO_INSTALL_ITEM_SEQ"
                        + " INNER JOIN ICM_BIDDER_QUOTATION q ON ii.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ"
                        + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM ri ON q.ICM_PR_ITEM_SEQ = ri.ICM_PR_ITEM_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE x ON ri.BGM_EXP_SEQ  = x.BGM_EXP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON ri.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                        + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                    + " WHERE ni.ICM_DN_SEQ = @seq"
                        + " AND q.SELECTED = @yes_status"
                    + " GROUP BY COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME), COALESCE(u.ICM_UNIT_NAME, 'รายการ')";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        strPoDescr = dr[i]["ITEM_NAME"].ToString()
                            + (Convert.ToInt32(dr[i]["PO_QTY"]) > 0 ? " จำนวน " + dr[i]["PO_QTY"].ToString() + " " + dr[i]["ICM_UNIT_NAME"].ToString() : "");
                            //+ " "  + " เป็นเงิน " + dr[i]["PO_AMOUNT"].ToString() + " บาท";
                        strDnDescr = dr[i]["ITEM_NAME"].ToString()
                            + (Convert.ToInt32(dr[i]["DN_QTY"]) > 0 ? " จำนวน " + dr[i]["DN_QTY"].ToString() + " " + dr[i]["ICM_UNIT_NAME"].ToString() : "");
                        if (i == 0)
                        {
                            strResult[0] += strPoDescr;
                            strResult[1] += strDnDescr;
                        }
                        else
                        {
                            if (i == dr.Count - 1)
                            {
                                strResult[0] += " และ " + strPoDescr;
                                strResult[1] += " และ " + strDnDescr;
                            }
                            else
                            {
                                strResult[0] += "," + strPoDescr;
                                strResult[1] += "," + strDnDescr;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public DataTable GetPurchaseRequestApproveMain(int seq, string buy_descr, string win_descr)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@buy_descr", buy_descr);
                dbManager.AddParameters(2, "@win_descr", win_descr);
                dbManager.AddParameters(3, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT pr.ICM_PR_NAME"
                        + ", pr.ICM_PR_REMARK"
                        + ", pm.BIDDING_STATUS"
                        + ", pm.ICM_PC_METHOD_NAME"
                        + ", pr.ICM_PR_REASON"
                        + ", pr.ICM_PC_METHOD_REASON"
                        + ", CASE WHEN pr.REQUIRE_DATE IS NOT NULL"
                            + " THEN cast(CONCAT('ภายในวันที่ ', F_FORMAT_BUDDHIST_DATE(pr.REQUIRE_DATE)) as char)"
                            + " ELSE cast(CONCAT('ภายใน ', pr.REQUIRE_DURATION , ' ' , dt.ICM_DUR_TYPE_NAME , 'นับจาก', dc.ICM_DUR_COUNT_NAME) as char)"
                        + " END AS DURATION"
                        + ", CONCAT(np.PREFIX_NAME, em.FIRST_NAME, ' ' , em.LAST_NAME) AS PURCHASE_NAME"
                        + ", (select job_site_name from hrm_mp_job_sites where job_site_seq = pr.purchase_job_site_seq) AS PURCHASE_JOB_SITE"
                        + ", mc.CLASS_NAME"
                        + ", mp.POS_NAME"
                        + ", fp.FIELD_POS_NAME"
                        + ", (select count(icm_pr_item_seq) from icm_purchase_request_item where icm_pr_seq = @seq) AS NUM_ITEM"
                        + ", FORMAT(COALESCE((select sum(coalesce(icm_pr_item_price * icm_pr_item_qty,0)) from icm_purchase_request_item where icm_pr_seq = @seq), 0), 2) AS PR_AMOUNT"
                        + ", FORMAT(COALESCE((select sum(q.net_amount) from icm_bidder_quotation q"
                            + " inner join icm_purchase_request_item i on q.icm_pr_item_seq = i.icm_pr_item_seq"
                            + " where i.icm_pr_seq = @seq"
                                + " and q.selected = @yes_status),0), 2)"
                        + " AS QUO_AMOUNT"
                        + ", pr.ICM_PR_SEQ"
                        + ", (select job_site_name from hrm_mp_job_sites where job_site_seq = (select dep_job_site_seq from icm_sys_control)) AS CURRENT_DEP"
                        + ", pt.ICM_PC_TYPE_NAME"
                        + ", FORMAT((select count(icm_bidder_seq) from icm_bidder where icm_pr_seq = @seq),0) AS NUM_BIDDER"
                        + ", @buy_descr AS BUY_DESCR"
                        + ", @win_descr AS WIN_DESCR"
                    + " FROM ICM_PURCHASE_REQUEST pr"
                        + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                        + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " LEFT JOIN ICM_SYS_DURATION_COUNT dc ON pr.ICM_DUR_COUNT_SEQ = dc.ICM_DUR_COUNT_SEQ"
                        + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON pr.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES em ON pr.PURCHASE_EMP_SEQ = em.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON em.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES mc ON em.CLASS_SEQ = mc.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS mp ON em.POS_SEQ = mp.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS fp ON mp.FIELD_POS_SEQ = fp.FIELD_POS_SEQ"
                    + " WHERE pr.ICM_PR_SEQ = @seq";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetPurchaseRequestApproveItem(int seq)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", FORMAT(p.ICM_PR_ITEM_PRICE, 2) AS PRICE"
                            + ", FORMAT(p.ICM_PR_ITEM_QTY, 0) AS QTY"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                            + ", p.ICM_PR_SEQ AS PR_TRAN_ID"
                            + ", FORMAT(p.ICM_PR_ITEM_PRICE * p.ICM_PR_ITEM_QTY, 2) AS SUM_PRICE"
                            + ", pm.BIDDING_STATUS"
                        + " FROM ICM_PURCHASE_REQUEST_ITEM p"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON p.ICM_PR_SEQ = pr.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ" 
                            + " INNER JOIN BGM_MAS_EXPENSE x ON p.BGM_EXP_SEQ  = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON p.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE p.ICM_PR_SEQ = @seq";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetPurchaseRequestApproveBidderItem(int seq)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT b.VAT_PERCENT"
                            + ", ct.ICM_CON_NAME_TH"
                            + ", ct.ICM_CON_SEQ"
                            + ", F_FORMAT_BUDDHIST_DATE(b.ICM_BIDDER_DATE) AS BIDDER_DATE"
                            + ", COALESCE(i.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", FORMAT(q.ITEM_QTY, 0) AS ITEM_QTY"
                            + ", FORMAT(q.ITEM_PRICE, 2) AS ITEM_PRICE"
                            + ", FORMAT(COALESCE(q.EXCLUDE_VAT_AMOUNT,0) + COALESCE(q.VAT_AMOUNT,0) - COALESCE(q.NET_AMOUNT,0), 2) AS ALL_DISCOUNT"
                            + ", FORMAT(q.NET_AMOUNT, 2) AS NET_AMOUNT"
                            + ", b.ICM_VAT_SEQ"
                            + ", v.ICM_VAT_NAME"
                            + ", b.ICM_PR_SEQ"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + " FROM ICM_BIDDER b"
                            + " INNER JOIN ICM_MAS_CONTACT ct ON ct.ICM_CON_SEQ = b.ICM_CON_SEQ"
                            + " INNER JOIN ICM_BIDDER_QUOTATION q ON b.ICM_BIDDER_SEQ = q.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM pi ON q.ICM_PR_ITEM_SEQ = pi.ICM_PR_ITEM_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON pi.BGM_EXP_SEQ  = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON pi.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                            + " LEFT JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ"
                        + " WHERE pi.ICM_PR_SEQ = @seq"
                            + " AND q.SELECTED = @yes_status";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetPurchaseRequestApproveCommittee(int seq)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT pr.ICM_PR_SEQ"
                            + ", CASE WHEN np.PREFIX_NAME IS NOT NULL" 
                                + " THEN CONCAT(np.PREFIX_NAME, em.FIRST_NAME, ' ' , em.LAST_NAME)"
                                + " ELSE rc.ICM_PR_COM_EX_NAME"
                            + " END AS COM_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", mc.CLASS_NAME"
                            + ", mp.POS_NAME"
                            + ", fp.FIELD_POS_NAME"
                            + ", cf.ICM_COM_FUNC_NAME"
                            + ", cp.ICM_COM_POS_NAME"
                            + ", cf.ICM_COM_FUNC_SEQ"
                        + " FROM ICM_PURCHASE_REQUEST pr"
                            + " INNER JOIN ICM_PURCHASE_REQUEST_COMMITTEE rc ON pr.ICM_PR_SEQ = rc.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_COMMITTEE_FUNCTION cf ON cf.ICM_COM_FUNC_SEQ = rc.ICM_COM_FUNC_SEQ"
                            + " INNER JOIN ICM_MAS_COMMITTEE_POSITION cp ON cp.ICM_COM_POS_SEQ = rc.ICM_COM_POS_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES em ON rc.EMP_SEQ = em.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON em.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " LEFT JOIN MAS_NAME_PREFIXES np ON em.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES mc ON em.CLASS_SEQ = mc.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS mp ON em.POS_SEQ = mp.POS_SEQ"
                            + " LEFT JOIN HRM_MP_FIELD_POSITIONS fp ON mp.FIELD_POS_SEQ = fp.FIELD_POS_SEQ"
                        + " WHERE pr.ICM_PR_SEQ = @seq"
                            + " AND rc.RECORD_STATUS <> @delete_status"
                        + " ORDER BY cf.ICM_COM_FUNC_SEQ, cp.ICM_COM_POS_SEQ, rc.ICM_PR_COM_SEQ";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetPurchaseOrderApproveMain(int seq)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT po.ICM_PO_SEQ"
                        + ", (select job_site_name from hrm_mp_job_sites where job_site_seq = (select dep_job_site_seq from icm_sys_control)) AS CURRENT_DEP"
                        + ", F_FORMAT_BUDDHIST_DATE(po.ORDER_DATE) AS PO_DATE"
                        + ", c.ICM_CON_NAME_TH"
                        + ", po.ICM_PO_NAME"
                        + ", F_FORMAT_BUDDHIST_DATE(b.ICM_BIDDER_DATE) AS BIDDER_DATE"
                        + ", (select sum(coalesce(i.net_amount,0))"
                            + " from icm_purchase_order_installment o"
                                + " inner join icm_purchase_order_installment_item i on o.icm_po_install_seq = i.icm_po_install_seq"
                            + " where o.icm_po_seq = @seq)"
                        + " AS SUM_NET_AMOUNT"
                        + ", (select sum(coalesce(i.exclude_vat_amount,0))"
                            + " from icm_purchase_order_installment o"
                                + " inner join icm_purchase_order_installment_item i on o.icm_po_install_seq = i.icm_po_install_seq"
                            + " where o.icm_po_seq = @seq)"
                        + " AS SUM_ITEM_PRICE"
                        + ", (select sum(coalesce(i.vat_amount,0))"
                            + " from icm_purchase_order_installment o"
                                + " inner join icm_purchase_order_installment_item i on o.icm_po_install_seq = i.icm_po_install_seq"
                            + " where o.icm_po_seq = @seq)"
                        + " AS SUM_VAT_AMOUNT"
                        + ", CASE WHEN po.REQUIRE_DATE IS NOT NULL"
                            + " THEN CONCAT('ภายในวันที่ ', F_FORMAT_BUDDHIST_DATE(po.REQUIRE_DATE))"
                            + " ELSE CONCAT('ภายใน ', po.REQUIRE_DURATION , ' ' , dt.ICM_DUR_TYPE_NAME)"
                        + " END AS DURATION"
                        + ", po.ICM_PO_REF_NO"
                        + ", (select distinct pt.icm_pc_type_name"
                            + " from icm_bidder_quotation q"
                                + " inner join icm_purchase_request_item i on q.icm_pr_item_seq = i.icm_pr_item_seq"
                                + " inner join icm_purchase_request r on i.icm_pr_seq = r.icm_pr_seq"
                                + " inner join icm_mas_purchase_type pt on r.icm_pc_type_seq = pt.icm_pc_type_seq"
                            + " where q.icm_bidder_seq = b.ICM_BIDDER_SEQ)"
                        + " AS ICM_PC_TYPE_NAME"
                        + ", b.VAT_PERCENT"
                    + " FROM ICM_PURCHASE_ORDER po"
                        + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ" 
                        + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                        + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON po.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                    + " WHERE po.ICM_PO_SEQ = @seq";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetDeliveryNoteApproveMain(int seq, string strPoDescr, string strDnDescr)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@po_descr", strPoDescr);
                dbManager.AddParameters(2, "@dn_descr", strDnDescr);
                dbManager.AddParameters(3, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT dn.ICM_DN_SEQ"
                        + ", dn.ICM_DN_NAME"
                        + ", st.ICM_PO_TYPE_NAME"
                        + ", po.ICM_PO_REF_NO"
                        + ", F_FORMAT_BUDDHIST_DATE(po.APPROVED_WHEN) AS PO_DATE"
                        + ", (select job_site_name from hrm_mp_job_sites where job_site_seq = (select dep_job_site_seq from icm_sys_control)) AS CURRENT_DEP"
                        + ", (select distinct pt.icm_pc_type_name"
                            + " from icm_bidder_quotation q"
                                + " inner join icm_purchase_request_item i on q.icm_pr_item_seq = i.icm_pr_item_seq"
                                + " inner join icm_purchase_request r on i.icm_pr_seq = r.icm_pr_seq"
                                + " inner join icm_mas_purchase_type pt on r.icm_pc_type_seq = pt.icm_pc_type_seq"
                            + " where q.icm_bidder_seq = b.ICM_BIDDER_SEQ)"
                        + " AS ICM_PC_TYPE_NAME"
                        + ", c.ICM_CON_NAME_TH"
                        + ", FORMAT((select sum(net_amount)"
                            + " from icm_bidder_quotation"
                            + " where icm_bidder_seq = b.ICM_BIDDER_SEQ), 2)"
                        + " AS PO_NET_AMOUNT"
                        //+ ", FORMAT((select sum(ii.net_amount)" 
                        //    + " from icm_purchase_installment i" 
                        //        + " inner join icm_purchase_installment_item ii on i.icm_po_install_seq = ii.icm_po_install_seq"
                        //    + " where i.icm_po_seq = po.ICM_PO_SEQ), 2)" 
                        //+ " AS PO_NET_AMOUNT"
                        + ", FORMAT((select sum(net_amount)" 
                            + " from icm_delivery_note_item" 
                            + " where icm_dn_seq = @seq), 2)" 
                        + " AS DN_NET_AMOUNT"
                        + ", F_FORMAT_BUDDHIST_DATE(dn.RECEIVE_DATE) AS RECEIVE_DATE"
                        + ", F_FORMAT_BUDDHIST_DATE(dn.DELIVERY_DATE) AS DN_DATE"
                        + ", dn.INVOICE_NUMBER"
                        + ", F_FORMAT_BUDDHIST_DATE(dn.INVOICE_DATE) AS INVOICE_DATE"
                        + ", CASE WHEN po.CAN_INSTALLMENT = @yes_status"
                            + " THEN FORMAT(oi.ICM_PO_INSTALL_NUMBER,0)"
                            + " ELSE NULL"
                        + " END AS INSTALL_NUMBER"
                        + ", @po_descr AS PO_DETAIL"
                        + ", @dn_descr AS DN_DETAIL"
                    + " FROM ICM_DELIVERY_NOTE dn"
                        + " INNER JOIN ICM_PURCHASE_ORDER po ON dn.ICM_PO_SEQ = po.ICM_PO_SEQ"
                        + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT oi ON dn.ICM_PO_INSTALL_SEQ = oi.ICM_PO_INSTALL_SEQ" 
                        + " INNER JOIN ICM_SYS_PO_TYPE st ON st.ICM_PO_TYPE_SEQ = po.ICM_PO_TYPE_SEQ"
                        + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                        + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                    + " WHERE dn.ICM_DN_SEQ = @seq";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable GetDeliveryNoteCommittee(int seq)
        {
            string strSQL;
            DataTable dt = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT c.ICM_DN_SEQ"
                            + ", CASE WHEN np.PREFIX_NAME IS NOT NULL"
                                + " THEN CONCAT(np.PREFIX_NAME, em.FIRST_NAME, ' ' , em.LAST_NAME)"
                                + " ELSE c.ICM_DN_COM_EX_NAME"
                            + " END AS COM_NAME"
                            + ", mc.CLASS_NAME"
                            + ", mp.POS_NAME"
                            + ", fp.FIELD_POS_NAME"
                            + ", cp.ICM_COM_POS_NAME"
                        + " FROM ICM_DELIVERY_NOTE_COMMITTEE c"
                            + " INNER JOIN ICM_MAS_COMMITTEE_POSITION cp ON cp.ICM_COM_POS_SEQ = c.ICM_COM_POS_SEQ"
                            + " INNER JOIN ICM_MAS_COMMITTEE_FUNCTION cf ON cf.ICM_COM_FUNC_SEQ = c.ICM_COM_FUNC_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES em ON c.EMP_SEQ = em.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON em.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " LEFT JOIN MAS_NAME_PREFIXES np ON em.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES mc ON em.CLASS_SEQ = mc.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS mp ON em.POS_SEQ = mp.POS_SEQ"
                            + " LEFT JOIN HRM_MP_FIELD_POSITIONS fp ON mp.FIELD_POS_SEQ = fp.FIELD_POS_SEQ"
                        + " WHERE c.ICM_DN_SEQ = @seq"
                            + " AND c.RECORD_STATUS <> @delete_status"
                            + " AND cf.ICM_COM_FUNC_NAME LIKE '%ตรวจ%'"
                        + " ORDER BY cp.ICM_COM_POS_SEQ, c.ICM_DN_COM_SEQ";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }
    }
}
